<?php
	class DbHelper{
		public $limit = "10";
		public $contactType=array('cell'=>'1','email'=>'2','homephone'=>'3','businessphone'=>'4');
		public function __construct(){}
		public function getLocationIdByLatLng($conn,$lat,$lng,$distance,$offset){
			$query = "SELECT *, round((3959 * acos(cos(radians(".$lat.")) * 
			cos(radians(job_location.latitude)) * cos( radians(job_location.longitude) 
			- radians(".$lng.")) + sin(radians(".$lat.")) * 
			sin(radians(job_location.latitude)))),2) AS distance FROM job_location HAVING distance <= $distance ORDER BY distance";
			return mysqli_query($conn,$query);
		}
		
		public function getJobsByLocationIds(array $locationId=array(),$conn,$offset,array $attribues = array()){
			$query="SELECT jobs.added_date,jobs.expire_date,jobs.isImage,jobs.image_url,jobs.image_url_thumbnail,jobs.jobs_id,jobs.description,job_location.statename,
			jobs.last_mod_date,job_location.city,job_location.statecode
			,job_location.latitude,job_location.longitude,languages.language,jobs_types.type 
			FROM jobs INNER JOIN job_location INNER JOIN languages INNER JOIN jobs_types ON 
			jobs.job_location_id= job_location.job_location_id AND jobs.languages_id=languages.languages_id 
			AND jobs.jobs_types_id=jobs_types.jobs_types_id
			WHERE jobs.job_location_id in(".implode(',',$locationId).") ";
			foreach($attribues as $key =>$value){
				$query .=" AND $key='$value'";
			}
			$query.="LIMIT ".$this->limit." OFFSET ".$offset*$this->limit;
			// this query will give us most of the job info
			return mysqli_query($conn,$query);
		}
		
		public function getContactInfoByJobId($jobId,$conn){
			$query="SELECT contact_value,contact_type_value FROM contacts INNER JOIN contact_type ON contacts.contact_type_id=contact_type.contact_type_id WHERE jobs_id=".$jobId;
			return mysqli_query($conn,$query);
		}
		
		public function saveJob($isImage,$zipcode,$phoneNumber,$jobType,$lang,$desc,$userId,$imageUrl,$thumbnail,$jobCategory,$conn){
			$jobLocation = $this->getJobLocationId($zipcode,$conn);
			$expiredDate =  date('Y-m-d H:i:s', strtotime("+30 days"));
			$addedDate = date('Y-m-d H:i:s');
			$modifiedDate = $addedDate;
			mysqli_autocommit($conn,FALSE);
			
			$query="INSERT INTO jobs (description,isImage,languages_id,";
			$query.="jobs_types_id,users_id,expire_date,job_location_id,image_url,added_date,last_mod_date,image_url_thumbnail,job_categories_id) ";
			$query.="VALUES ('$desc',$isImage,$lang,$jobType,$userId,'$expiredDate',$jobLocation,'$imageUrl','$addedDate','$modifiedDate','$thumbnail',$jobCategory);";
        
                        $firstSuccess= mysqli_query($conn,$query);
			$jobId = mysqli_insert_id($conn);
			$typeId =$this->contactType['cell'];
			$phoneInsert ="INSERT INTO contacts (contact_type_id,contact_value,jobs_id) VALUES ($typeId,'$phoneNumber',$jobId);";
			$secondSuccess = mysqli_query($conn,$phoneInsert);
                        $success = $firstSuccess ===TRUE && $secondSuccess===TRUE;
                        if(!$success){
                                 mysqli_rollback($conn);
                                return false;
			}else{
                               mysqli_commit($conn);
				return true;
			}
			
		}
		
		public function getLocationIdByZipcode($zipcode,$con){
			$query="SELECT job_location FROM job_location WHERE zipcode='$zipcode'";
			$result = mysqli_query($query,$con);
			while($row = mysqli_fetch_array($results)){
				return $row['job_location_id'];
			}
		}
		
		public function getLatLongByZipcode($zipcode,$con){
			$query="SELECT latitude,longitude FROM job_location WHERE zipcode='$zipcode'";
			$results = mysqli_query($con,$query);
			while($row = mysqli_fetch_array($results)){
				$lat= $row['latitude'];
				$lng = $row['longitude'];
				return array('lat'=>$lat,'lng'=>$lng);
			}
		}
		
		public function authUser($phoneNumber,$pwd,$con){
		
			$query = "SELECT * FROM users WHERE phone_number='$phoneNumber' AND password='$pwd'";
			 $result = mysqli_query($con,$query);
			 if($result){
                                if(mysqli_num_rows($result)>0){
                                    return $result;
                         }else{
                             return false;
                         }
			 }else{
				return false;
			 }
		}
		
		public function saveUser($phoneNumber,$pwd,$con){
			
			$query="INSERT INTO users (phone_number,password,isActive) VALUES ('$phoneNumber','$pwd',1)";
			error_log($query);
			$result = mysqli_query($con,$query);
			if($result){
				return true;
			}else{
				return false;
			}
		}
		
		private function getJobLocationId($zipcode,$conn){
			$query="SELECT job_location_id FROM job_location WHERE zipcode=$zipcode";
			$result = mysqli_query($conn,$query);
			$location_id;
			if($result){
				while($row = mysqli_fetch_array($result)){
						$location_id= $row['job_location_id'];
				}
			}
			return $location_id;
		}
		
		public function getSalt($conn){
			$query = "SELECT salt FROM auth";
			$result = mysqli_query($conn,$query);
			if($result){
				while($row=mysqli_fetch_array($result)){
					$salt = $row['salt'];
				}
			}
			return $salt;
		}
		
		public function getJobCategories($con){
			$query="SELECT * FROM job_categories";
			$result = mysqli_query($con,$query);
			return $result;
		}
	}
?>